clear

*Load Data
use "./data_geo.dta", clear

*****************************************************************************
*** Setup: see Kashyap & Stein (2000) and/or Den Haan, Sumner, Yamashiro (2002) for details ***

*Drop non-states 
drop if rssd9200 == "0"
keep if  rssd9210>0 & rssd9210< 57

*Drop Delaware and/or South Dakota (see Jayaratne & Strahan 1998 JLE)
*drop if rssd9210 == 10
*drop if rssd9210 == 46

*Sample period
keep if year_q < tq(1995q1)

*Positive Assets
keep if rcfd2170 > 0

*Commercial Banks Only
keep if rssd9048 == 200 

*Insured
*keep if rssd9424 == 1 | rssd9424 == 2 | rssd9424 == 6
drop if rssd9424 == 0

*Merger
drop if MERGE_CD != .

*Set panel data
xtset rssd9001 year_q

*Fix Total Loans pre-1984 (insert before calcuating loans2 above)
gen loans2 = rcfd1400
replace loans2 = rcfd1400+rcfd2165 if year_q < tq(1984q1)

*Put loans and loan components in real terms
gen c_p_i = cpi/100
gen r_loans = loans2/c_p_i 
gen r_ci = rcfd1600/c_p_i
gen r_rlest = rcfd1410/c_p_i
gen r_con = rcfd1975/c_p_i
gen r_ag = rcfd1590/c_p_i

*Log difference 
gen loans = ln(r_loans) - ln(L.r_loans)
gen ci = ln(r_ci) - ln(L.r_ci) 
gen re = ln(r_rlest) - ln(L.r_rlest) 
gen con = ln(r_con) - ln(L.r_con)
gen ag = ln(r_ag) - ln(L.r_ag) 

*Drop outliers
sort year_q
by year_q: egen sdv = sd(loans)
drop if loans >= 5*sdv
drop if loans <= -5*sdv

*Loan Component Outliers
gen ci_share = rcfd1600/loans2
*drop if ci_share < 0.05
gen re_share = rcfd1410/loans2
*drop if re_share < 0.05
gen con_share = rcfd1975/loans2
*drop if con_share < 0.05
gen ag_share = rcfd1590/loans2
*drop if ag_share < 0.05

*Four Consecutive Quarters of Loan Growth
*ssc install tsspell
tsspell, c(loans > 0)
sort rssd9001
by rssd9001: egen maxrun = max(_seq)
drop if maxrun < 4

*Percentiles
sort year_q
by year_q: egen p95 = pctile(rcfd2170), p(95)
by year_q: egen p99 = pctile(rcfd2170), p(99)

*Generate Quarter/Year/timetrend Series
sort rssd9001 year_q
gen Quarter = quarter(date2)
gen Year = year(date2) 
egen time = group(year_q)

*Choose to keep (or drop) year deregulation was implemented 
replace intra = 1 if intra == .
replace inter =1 if inter == .

* Generate Interaction Terms
gen rr_intra = rr*intra
gen rr_intra1 = L1.rr*intra
gen rr_intra2 = L2.rr*intra
gen rr_intra3 = L3.rr*intra
gen rr_intra4 = L4.rr*intra

gen rr_inter = rr*inter
gen rr_inter1 = L1.rr*inter
gen rr_inter2 = L2.rr*inter
gen rr_inter3 = L3.rr*inter
gen rr_inter4 = L4.rr*inter

*******************************************************************************
*** Bank-level Characteristics ***

* BHC Status
gen bhc = 0
replace bhc = 1 if rssd9379 != 0

* Size (total assets)
gen Size = ln(rcfd2170)

* Liquidity ratio
gen Liq = rcfd0010/rcfd2950

*Equity raito
gen Cap = rcfd3210/rcfd2170

* Securities
gen Sec = rcfd0390 + rcfd1350
gen sec84 = rcfd0400 + rcfd0600 + rcfd0900 + rcfd0380 + rcfd1350
replace Sec = sec84 if year_q < tq(1984q1) 

*******************************************************************************
*** Bank Market Structure variables ***

********************
* (1) Lerner Index

*Create variables for LI estimation
gen P = riad4000/rcfd2170
gen C = riad4170 + riad4135 + riad4217
gen Q = rcfd2170
gen W1 = riad4217/rcfd2170
gen W2 = riad4135/rcfd2170
gen W3 = riad4170/rcfd2200
gen c = ln(C)
gen q = ln(Q)
gen q2 = q^2
gen w1 = ln(W1)
gen w2 = ln(W2)
gen w3 = ln(W3)
gen w1w1 = w1*w1
gen w1w2 = w1*w2
gen w1w3 = w1*w3
gen w2w1 = w2*w1
gen w2w2 = w2*w2
gen w2w3 = w2*w3
gen w3w1 = w3*w1
gen w3w2 = w3*w2
gen w3w3 = w3*w3
gen w1q = w1*q
gen w2q = w2*q
gen w3q = w3*q
gen CQ = C/Q
gen w1_w3 = ln(W1/W3)
gen w2_w3 = ln(W2/W3)
constraint 1 w1+w2+w3=1
constraint 2 w1q+w2q+w3q=0
constraint 3 w1w1+w1w2+w1w3=0
constraint 4 w1w2+w2w2+w2w3=0
constraint 5 w1w3+w2w3+w3w3=0

*Demean for bank FE
sort rssd9001 year_q
by rssd9001: egen mean_c = mean(c)
by rssd9001 year_q: gen dm_c = c - mean_c

sort rssd9001 year_q
by rssd9001: egen mean_q = mean(q)
by rssd9001 year_q: gen dm_q = q - mean_q

sort rssd9001 year_q
by rssd9001: egen mean_q2 = mean(q2)
by rssd9001 year_q: gen dm_q2 = q2 - mean_q2

sort rssd9001 year_q
by rssd9001: egen mean_w1 = mean(w1)
by rssd9001 year_q: gen dm_w1 = w1 - mean_w1

sort rssd9001 year_q
by rssd9001: egen mean_w2 = mean(w2)
by rssd9001 year_q: gen dm_w2 = w2 - mean_w2

sort rssd9001 year_q
by rssd9001: egen mean_w3 = mean(w3)
by rssd9001 year_q: gen dm_w3 = w3 - mean_w3

sort rssd9001 year_q
by rssd9001: egen mean_w1w1 = mean(w1w1)
by rssd9001 year_q: gen dm_w1w1 = w1w1 - mean_w1w1

sort rssd9001 year_q
by rssd9001: egen mean_w1w2 = mean(w1w2)
by rssd9001 year_q: gen dm_w1w2 = w1w2 - mean_w1w2

sort rssd9001 year_q
by rssd9001: egen mean_w1w3 = mean(w1w3)
by rssd9001 year_q: gen dm_w1w3 = w1w3 - mean_w1w3

sort rssd9001 year_q
by rssd9001: egen mean_w2w1 = mean(w2w1)
by rssd9001 year_q: gen dm_w2w1 = w2w1 - mean_w2w1

sort rssd9001 year_q
by rssd9001: egen mean_w2w2 = mean(w2w2)
by rssd9001 year_q: gen dm_w2w2 = w2w2 - mean_w2w2

sort rssd9001 year_q
by rssd9001: egen mean_w2w3 = mean(w2w3)
by rssd9001 year_q: gen dm_w2w3 = w2w3 - mean_w2w3

sort rssd9001 year_q
by rssd9001: egen mean_w3w1 = mean(w3w1)
by rssd9001 year_q: gen dm_w3w1 = w3w1 - mean_w3w1

sort rssd9001 year_q
by rssd9001: egen mean_w3w2 = mean(w3w2)
by rssd9001 year_q: gen dm_w3w2 = w3w2 - mean_w3w2

sort rssd9001 year_q
by rssd9001: egen mean_w3w3 = mean(w3w3)
by rssd9001 year_q: gen dm_w3w3 = w3w3 - mean_w3w3

sort rssd9001 year_q
by rssd9001: egen mean_w1q = mean(w1q)
by rssd9001 year_q: gen dm_w1q = w1q - mean_w1q

sort rssd9001 year_q
by rssd9001: egen mean_w2q = mean(w2q)
by rssd9001 year_q: gen dm_w2q = w2q - mean_w2q

sort rssd9001 year_q
by rssd9001: egen mean_w3q = mean(w3q)
by rssd9001 year_q: gen dm_w3q = w3q - mean_w3q

* LI Estimation
cnsreg dm_c dm_q dm_q2 dm_w1 dm_w2 dm_w3 dm_w1w1 dm_w1w2 dm_w1w3 dm_w2w1 dm_w2w2 dm_w2w3 dm_w3w1 dm_w3w2 dm_w3w3 dm_w1q dm_w2q dm_w3q i.year_q, c(1-5)
gen alpha1 = _b[dm_q] 
gen alpha2 = _b[dm_q2] 
gen gamma1 = _b[dm_w1q] 
gen gamma2 = _b[dm_w2q]
gen gamma3 = _b[dm_w3q] 
gen MC = CQ*(alpha1 + alpha2*q + gamma1*w1 + gamma2*w2 + gamma3*w3)
gen lerner = (P - MC)/P
replace lerner = 0 if lerner < 0
replace lerner = 1 if lerner > 1 & lerner != .
replace lerner = (lerner[_n-1]+lerner[_n+1])/2 if lerner==. & Year < 1983

gen inter_lern = inter*L.lerner
gen rr_lern = rr*L.lerner
gen rr_lern1 = L1.rr*L.lerner
gen rr_lern2 = L2.rr*L.lerner
gen rr_lern3 = L3.rr*L.lerner
gen rr_lern4 = L4.rr*L.lerner

******************
*(2) County HHI
sort rssd9210 rssd9150 year_q
by rssd9210 rssd9150 year_q: egen DEP_c = sum(rcfd2200)
by rssd9210 rssd9150 year_q: egen ASSETS_c = sum(rcfd2170)

gen share_c = rcfd2200/DEP_c
gen share_a_c = rcfd2170/ASSETS_c

gen share_sq_c = share_c^2
gen share_a_sq_c = share_a_c^2

sort rssd9210 rssd9150 year_q  
by rssd9210 rssd9150 year_q: egen hhic = sum(share_sq_c) 
by rssd9210 rssd9150 year_q: egen hhic_a = sum(share_a_sq_c) 

sort rssd9001 year_q
gen inter_hhic = inter*hhic_a
gen rr_hhic = rr*hhic_a
gen rr_hhic1 = L1.rr*hhic_a
gen rr_hhic2 = L2.rr*hhic_a
gen rr_hhic3 = L3.rr*hhic_a
gen rr_hhic4 = L4.rr*hhic_a

********************
*(3) State HHI
sort rssd9210 year_q  
by rssd9210 year_q: egen DEP_s = sum(rcfd2200)
by rssd9210 year_q: egen ASSETS_s = sum(rcfd2170)

gen share_s = rcfd2200/DEP_s
gen share_a_s = rcfd2170/ASSETS_s

gen share_sq_s = share_s^2
gen share_a_sq_s = share_a_s^2

by rssd9210 year_q: egen hhis = sum(share_sq_s) 
by rssd9210 year_q: egen hhis_a = sum(share_a_sq_s) 

sort rssd9001 year_q

gen inter_hhi = inter*hhis_a
gen rr_hhis = rr*hhis_a
gen rr_hhis1 = L1.rr*hhis_a
gen rr_hhis2 = L2.rr*hhis_a
gen rr_hhis3 = L3.rr*hhis_a
gen rr_hhis4 = L4.rr*hhis_a

*******************************************************************************
*** Generate Avg Loan Rate ***

gen int_income = riad4010
replace int_income = int_income[_n+1]/2 if Quarter == 1 & Year < 1983
replace int_income = (int_income[_n-1]+int_income[_n+1])/2 if Quarter == 3 & Year < 1983

gen int_income2 = int_income - L.int_income if Quarter > 1 
replace int_income2 = int_income if int_income2 == .

gen avg_rate2 = int_income2/loans2
gen avg_rate = avg_rate2*4

replace avg_rate = . if avg_rate >= 4 | avg_rate < 0

*******************************************************************************
*** Summary Stats ***

* Table 1
sum loans avg_rate ci re con ci_share re_share con_share rcfd2170 Sec Liq Cap bhc lerner hhic_a hhis_a 
sum loans avg_rate ci re con ci_share re_share con_share rcfd2170 Sec Liq Cap bhc lerner hhic_a hhis_a if rcfd2170 < p95
sum loans avg_rate ci re con ci_share re_share con_share rcfd2170 Sec Liq Cap bhc lerner hhic_a hhis_a if rcfd2170 >= p95

*******************************************************************************

